

import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.net.Socket;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Vector;

public class ClientHandler extends Thread{

	Socket socket;
	Connection myConnection;
    Statement statement = null;
    ResultSet rs = null;
    
    private String followers = "followers";
    private String followings = "followings";
    private int updating = 1;
    private int notUpdating = 0;
    
    static Vector<Integer> userList = new Vector<Integer>();
    Vector<ClientHandler> cHandlersList = TwitterServer.cHandlersList;
    
	public ClientHandler(Connection myConnection, Socket socket, String clientHandlerId){
		this.myConnection = myConnection;
		this.socket = socket;
		this.setName(clientHandlerId);
	}
	
	@Override
	public synchronized void run() {
		try {
			while(true){
				ObjectInputStream ois = new ObjectInputStream(socket.getInputStream());
				// Read userobj from client
				Command command = (Command) ois.readObject();
				if(command.commandId == 1){
					User user = (User) command.data;
					System.out.println("Thread:" + Thread.currentThread().getName() + "  saving in DB userId: " +user.id);
					if(user.screenName != null){
						checkNewInfo(user);
					}
					// Insert new users in to db
					checkAndInsertUsers(user.followers, user.id, followers);
					checkAndInsertUsers(user.following, user.id, followings);
					System.out.println("UserList new size: "+userList.size());
					// waking up the updater if he's sleeping 
					synchronized(userList){
						userList.notify();
					}
					// Insert twitt into db
					insertTwitt(user.id, user.twitts);
					setLastUpdate(user.id);
					setUpdating(user.id, notUpdating);
					System.out.println("Thread No. " + Thread.currentThread().getName() + " finnished updating userId: " +user.id);
				}
				if(command.commandId == 0){
					Command nextUser = getNextUser();
					//for testing
					User user = (User) nextUser.data;
					if(user != null){
					System.out.println("Thread No. " + Thread.currentThread().getName() + " delivering userId: " +user.id);
					}
					//
					ObjectOutputStream oos = new ObjectOutputStream(socket.getOutputStream());
					oos.writeObject(nextUser);
					oos.close();
				}
				ois.close();
				socket.close();
				try {
					synchronized (cHandlersList) {
						cHandlersList.add(this);
					}
					System.out.println(cHandlersList.size()+" Threads are waiting in the list.");
					wait();
				} catch (InterruptedException e) {
					e.printStackTrace();
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	/**
	 * check if a user has his info in the db
	 * @param user
	 * @throws SQLException
	 */
	private void checkNewInfo(User user) throws SQLException {
		
		if(user.description!= null)user.description = this.deleteApostrophe(user.description);
		if(user.location!= null)user.location = this.deleteApostrophe(user.location);
		if(user.url!= null)user.url = this.deleteApostrophe(user.url);
		statement = myConnection.createStatement();
		statement.executeUpdate("UPDATE user " +
									"SET name='"+user.name+"', screen_name='"+user.screenName+"', location='"+user.location+"', description='"+user.description+"', url='"+user.url+"'" +
									"WHERE id='"+user.id+"'");
	}
	/**
	 * get the next user from db who was first updated along with the time of the last twitt.
	 * if non is available send back the command 2.
	 * @return
	 * @throws SQLException
	 * @throws InterruptedException 
	 */
	public Command getNextUser() throws SQLException {
	
		statement = myConnection.createStatement();
		rs = statement.executeQuery("SELECT id, last_update FROM user WHERE updating = 0 GROUP BY last_update");
		if(!rs.next()){
			// return 2 when all users are taken  
			return new Command(null,2);
		}
		String owner = rs.getString(1);
		rs = statement.executeQuery("SELECT name,screen_name,location,description,url FROM user where id = " + owner);
		rs.next();
		User user = new User(owner,rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),null,null);
		// set the user updating
		setUpdating(owner, updating);
		String twittQuery = "SELECT max(id) FROM twitts WHERE owner = " + owner;
		rs = statement.executeQuery(twittQuery);
		rs.next();
		String id = rs.getString(1);
	    user.lastTwitt = id;
	    Command com = new Command(user, 0);
	 
	    rs.close();
		return com;
	}
	/**
	 * insert the new twitts from a user to the twitts table in the db
	 * @param userId
	 * @param twitts
	 * @throws SQLException
	 */
	public void insertTwitt(String userId, Twitt[] twitts){
		try {
			statement = myConnection.createStatement();
			for(Twitt twitt : twitts) {
				Timestamp published = Timestamp.valueOf(twitt.published);
				if(twitt.content!= null)twitt.content = this.deleteApostrophe(twitt.content);
				if(twitt.source!= null)twitt.source = this.deleteApostrophe(twitt.source);
				if(twitt.place!= null)twitt.place = this.deleteApostrophe(twitt.place);
				String query = "INSERT INTO twitts VALUES('"+twitt.id+"', '"+userId+"', '"+twitt.content+"', '"+published+"'," +
						" '"+getTime()+"', '"+twitt.crawler+"', '"+twitt.source+"', '"+twitt.place+"')";
		    	statement.executeUpdate(query);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}catch(IllegalArgumentException e){
			e.printStackTrace();
		}
		
	}
	/**
	 * add the new users from the user's following,followers lists 
	 * and save it in the user table. also update the following, followers lists of the user.
	 * @param listOfUsers
	 * @param id
	 * @param table
	 * @throws SQLException 
	 * @throws SQLException
	 */
	public void checkAndInsertUsers(String listOfUsers, String id, String table) throws SQLException {
		if(listOfUsers.length() < 3) return; 
		listOfUsers = listOfUsers.substring(0, listOfUsers.length()-1);
		String[] users = listOfUsers.split(",");
		String empty = "";
		statement = myConnection.createStatement();
		
		for(String user1 : users) {
			if(user1 == empty) continue;
			if(!userList.contains(new Integer(user1))){
				userList.add(new Integer(user1));
		   	}
		}
		rs = statement.executeQuery("SELECT * FROM " + table + " WHERE id = " +id );
		if(!rs.next()){
			statement.executeUpdate("INSERT INTO " +table+ " VALUES ('"+id+"', '"+listOfUsers+"')");
		}else{
			statement.executeUpdate("UPDATE " +table+ " SET " +table+ " = '"+listOfUsers+"' WHERE id = " + id);
		}
	}
	/**
	 * get the current date and time
	 * @return the current time as a TimeStamp object
	 */
	public Timestamp getTime(){
		java.util.Date today = new java.util.Date();
		return new java.sql.Timestamp(today.getTime());
	}
	/**
	 * set the updating status of the user. 0 for available and 1 for busy.
	 * @param id
	 * @param num
	 * @throws SQLException
	 */
	private void setUpdating(String id, int num) throws SQLException {
		statement = myConnection.createStatement();
		statement.executeUpdate("UPDATE user SET updating = '"+num+"' WHERE id =" + id);
	}
	/**
	 * set the time that the user was last updated
	 * @param id
	 * @throws SQLException
	 */
	private void setLastUpdate(String id) throws SQLException {
		Timestamp lastUpdate = this.getTime();
		statement = myConnection.createStatement();
		statement.executeUpdate("UPDATE user SET last_update =  '"+lastUpdate+"'  WHERE id = '"+id+"'");
	}
	public String deleteApostrophe(String string){
		string = string.replaceAll("'", "");
		return string;
	}
	/**
	 * set the thread socket
	 * @param socket
	 */
	public synchronized void setSocket(Socket socket){
		this.socket = socket;
		notify();
	}
	
}
